﻿using System;
using System.Data;
using System.Data.SqlClient;
using AgnisEbmtTranslator.DataSets;
using AgnisEbmtTranslator.DataSets.MappingDataSetTableAdapters;

namespace AgnisEbmtTranslator
{
    /// <summary>
    /// Provides methods to work with the mapping information data.
    /// </summary>
    public static class MappingInfoDAL
    {
        #region local members
        private static String currentMappingInfoConnectionString;
        static System.Data.SqlClient.SqlConnection mySqlConnection;
        // Mapping information table adapters.
        static FieldValuesTableAdapter taMappingInfo = new FieldValuesTableAdapter();
        static RecordsSelectionTableAdapter taRecordsSelection = new RecordsSelectionTableAdapter();
        static RecordsSelectionGroupsTableAdapter taRecordsSelectionGroups = new RecordsSelectionGroupsTableAdapter();
        static RSelTablesCounterTableAdapter taRSelTablesCounter = new RSelTablesCounterTableAdapter();
        static ConditionTablesRelatedListTableAdapter taConditionTablesRelatedList = new ConditionTablesRelatedListTableAdapter();
        static RecordsSelectionFiltersTableAdapter taRecordsSelectionFilters = new RecordsSelectionFiltersTableAdapter();
        static RecordsSelectionFunctionsTableAdapter taRecordsSelectionFunctions = new RecordsSelectionFunctionsTableAdapter();
        static MappingConditionsTableAdapter taConditions = new MappingConditionsTableAdapter();
        static MappingConditionsGroupsTableAdapter taConditionsGroups = new MappingConditionsGroupsTableAdapter();
        static ConditionGroupsListTableAdapter taConGroupsList = new ConditionGroupsListTableAdapter();
        static ConditionsSubGroupsListTableAdapter taConSubGroupsList = new ConditionsSubGroupsListTableAdapter();
        static MappingQValueQuestionsListTableAdapter taMapQValueQuestionsList = new MappingQValueQuestionsListTableAdapter();
        // Agnis forms Information table adapters.
        static FormsListTableAdapter taFormsList = new FormsListTableAdapter();
        static ModulesListTableAdapter taModulesList = new ModulesListTableAdapter();
        static NormalizedQuestionsListTableAdapter taNormalizedQuestionsList = new NormalizedQuestionsListTableAdapter();
        static NonNormalizedQuestionsListTableAdapter taNonNormalizedQuestionList = new NonNormalizedQuestionsListTableAdapter();
        static NormalizedValuesListTableAdapter taNormalizedValuesList = new NormalizedValuesListTableAdapter();
        static NonNormalizedValuesListTableAdapter taNonNormalizedValuesList = new NonNormalizedValuesListTableAdapter();
        // Agnis objects used during the data translation.
        public static AgnisDAL.ServiceAgnis.Form aForm = new AgnisDAL.ServiceAgnis.Form();
        public static AgnisDAL.ServiceAgnis.FormRevision aFormRevision = new AgnisDAL.ServiceAgnis.FormRevision();

        #endregion

        /// <summary>
        /// Set the current connection string for the MappingInfo database.
        /// </summary>
        /// <param name="cString"></param>
        public static void SetCurrentMappingInfoConnectionString(String cString)
        {
            currentMappingInfoConnectionString = cString;
        }

        /// <summary>
        /// Load the mapping information from the database.
        /// Fill the mapping info objects related to the translation logic.
        /// </summary>
        /// <param name="dsMappingInfo"></param>
        /// <returns>True if Fill was ok.</returns>
        public static bool LoadMappingInfo(MappingDataSet dsMappingInfo)
        {
            mySqlConnection = new SqlConnection(currentMappingInfoConnectionString);

            taMappingInfo.Connection = mySqlConnection;
            taRecordsSelection.Connection = mySqlConnection;
            taRecordsSelectionGroups.Connection = mySqlConnection;
            taRSelTablesCounter.Connection = mySqlConnection;
            taConditionTablesRelatedList.Connection = mySqlConnection;
            taRecordsSelectionFilters.Connection = mySqlConnection;
            taRecordsSelectionFunctions.Connection = mySqlConnection;
            taConditions.Connection = mySqlConnection;
            taConditionsGroups.Connection = mySqlConnection;
            taConGroupsList.Connection = mySqlConnection;
            taConSubGroupsList.Connection = mySqlConnection;

            //We load the mapping information dataset.
            taMappingInfo.Fill(dsMappingInfo.FieldValues);
            taRecordsSelection.Fill(dsMappingInfo.RecordsSelection);
            taRecordsSelectionGroups.Fill(dsMappingInfo.RecordsSelectionGroups);
            taRSelTablesCounter.Fill(dsMappingInfo.RSelTablesCounter);
            taConditionTablesRelatedList.Fill(dsMappingInfo.ConditionTablesRelatedList);
            taRecordsSelectionFilters.Fill(dsMappingInfo.RecordsSelectionFilters);
            taRecordsSelectionFunctions.Fill(dsMappingInfo.RecordsSelectionFunctions);
            taConditions.Fill(dsMappingInfo.MappingConditions);
            taConditionsGroups.Fill(dsMappingInfo.MappingConditionsGroups);
            taConGroupsList.Fill(dsMappingInfo.ConditionGroupsList);
            taConSubGroupsList.Fill(dsMappingInfo.ConditionsSubGroupsList);
            return true;
        }

        /// <summary>
        /// Loads the mapping information from the database,
        /// corresponding to the information of the Agnis structure.
        /// </summary>
        /// <param name="dsFormInfo"></param>
        /// <returns>True if Fill was ok.</returns>
        public static bool LoadAgnisFormsInfo(MappingDataSet dsFormInfo)
        {
            mySqlConnection = new SqlConnection(currentMappingInfoConnectionString);

            //We load the Agnis form information tables.
            taFormsList.Connection = mySqlConnection;
            taModulesList.Connection = mySqlConnection;
            taNormalizedQuestionsList.Connection = mySqlConnection;
            taNonNormalizedQuestionList.Connection = mySqlConnection;
            taNormalizedValuesList.Connection = mySqlConnection;
            taNonNormalizedValuesList.Connection = mySqlConnection;
            taMapQValueQuestionsList.Connection = mySqlConnection;

            taFormsList.Fill(dsFormInfo.FormsList);
            taModulesList.Fill(dsFormInfo.ModulesList);
            taNormalizedQuestionsList.Fill(dsFormInfo.NormalizedQuestionsList);
            taNonNormalizedQuestionList.Fill(dsFormInfo.NonNormalizedQuestionsList);
            taNormalizedValuesList.Fill(dsFormInfo.NormalizedValuesList);
            taNonNormalizedValuesList.Fill(dsFormInfo.NonNormalizedValuesList);
            taMapQValueQuestionsList.Fill(dsFormInfo.MappingQValueQuestionsList);
            return true;
        }

        #region Working with Agnis database objects and structure
        /// <summary>
        /// Gets the list of module rows for the form id send as parameter.
        /// </summary>
        /// <param name="dsFormInfo">The MappingDataset object from where to retrieve the module info.</param>
        /// <param name="formPublicID">The form PublicID.</param>
        /// <param name="formVersion">The form version.</param>
        /// <returns>A dataview object containing the list of modules belonging to the form.</returns>
        public static DataView GetModulesListForThisForm(MappingDataSet dsFormInfo, int formPublicID, decimal formVersion)
        {
            DataView modulesListFilter = new DataView(dsFormInfo.ModulesList);
            modulesListFilter.RowFilter = "[caDSR Form Public ID]='" + formPublicID + "' AND "
                                            + "[caDSR Form Version]='" + formVersion + "'";

            if (modulesListFilter.Count > 0)
            { return modulesListFilter; }
            else
            { return null; }
        }

        /// <summary>
        /// Get list of questions belonging to a module.
        /// </summary>
        /// <param name="dsFormInfo"></param>
        /// <param name="moduleRow"></param>
        /// <returns>A DataView object containing all the questionsList rows for this module.</returns>
        public static DataView GetQuestionsListForThisModule(MappingDataSet dsFormInfo, MappingDataSet.ModulesListRow moduleRow)
        {
            if (moduleRow.Normalized == "Yes") // If normalized
            {
                DataView QuestionsListFilter = new DataView(dsFormInfo.NormalizedQuestionsList);
                QuestionsListFilter.RowFilter = "[Module Public ID]='" + moduleRow.Module_Public_ID + "'";
                if (QuestionsListFilter.Count > 0)
                { return QuestionsListFilter; }
                else
                { return null; }
            }
            else // If non-Normalized
            {
                DataView QuestionsListFilter = new DataView(dsFormInfo.NonNormalizedQuestionsList);
                QuestionsListFilter.RowFilter = "[Module Public ID]='" + moduleRow.Module_Public_ID + "'";
                if (QuestionsListFilter.Count > 0)
                { return QuestionsListFilter; }
                else
                { return null; }
            }

        }

        /// <summary>
        /// When a module is normalized, it has a MappingQuestion to which all secondary questions are related.
        /// This function gets the list of values for the MappingQuestion of this module.
        /// </summary>
        /// <param name="dsFormInfo"></param>
        /// <param name="moduleRow"></param>
        /// <returns>A DataView object containing the list of values for this question.</returns>
        public static DataView GetValuesListForThisMappingQuestion(MappingDataSet dsFormInfo, MappingDataSet.ModulesListRow moduleRow)
        {
            DataView normalizedValuesList = new DataView(dsFormInfo.NormalizedValuesList);
            normalizedValuesList.RowFilter = "[CDE Public ID]='" + moduleRow.Mapping_CDE_Public_ID + "' AND"
                                                + "[caDSR Form Public ID]='" + moduleRow.caDSR_Form_Public_ID + "'";
       
            if (normalizedValuesList.Count > 0)
            { return normalizedValuesList; }
            else
            { return null; }
        }

        /// <summary>
        /// Get the questions list for this normalized module.
        /// </summary>
        /// <param name="dsFormInfo"></param>
        /// <param name="mappingQValue"></param>
        /// <returns>A DataView object containing the questionList rows for this module.</returns>
        public static DataView GetQuestionsListForThisNormalizedModule(MappingDataSet dsFormInfo, MappingDataSet.NormalizedValuesListRow mappingQValue)
        {
            DataView qValueQuestionsList = new DataView(dsFormInfo.MappingQValueQuestionsList);
            qValueQuestionsList.RowFilter = "[Module Public ID]='" + mappingQValue.Module_Public_ID + "' AND "
                                                + "[Module Version]='" + mappingQValue.Module_Version + "' AND "
                                                + "[CDE Public ID]='" + mappingQValue.CDE_Public_ID + "' AND "
                                            + "[CDE Version]='" + mappingQValue.CDE_Version + "' AND "
                                            + "[Value Meaning Public ID]='" + mappingQValue.Value_Meaning_Public_ID + "' AND "
                                            + "[Value Meaning Version]='" + mappingQValue.Value_Meaning_Version + "'";
            if (qValueQuestionsList.Count > 0)
            { return qValueQuestionsList; }
            else
            { return null; }
        }

        /// <summary>
        /// Get the permissible values for this normalized question.
        /// </summary>
        /// <param name="dsFormInfo"></param>
        /// <param name="questionRow"></param>
        /// <returns>A DataView object containing all the pValueRows for this question.</returns>
        public static DataView GetPermissibleValuesForThisNormalizedQuestion(MappingDataSet dsFormInfo, MappingDataSet.MappingQValueQuestionsListRow questionRow, MappingDataSet.ModulesListRow moduleRow)
        {
            DataView normalizedValuesList = new DataView(dsFormInfo.NormalizedValuesList);
            normalizedValuesList.RowFilter = "[Module Public ID]='" + questionRow.Module_Public_ID + "' AND "
                                                + "[Module Version]='" + questionRow.Module_Version + "' AND "
                                                + "[CDE Public ID]='" + questionRow.CDE_Public_ID_2 + "' AND "
                                                + "[CDE Version]='" + questionRow.CDE_Version_2 + "' AND "
                                                + "[caDSR Form Public ID]='" + moduleRow.caDSR_Form_Public_ID + "'";
            
            if (normalizedValuesList.Count > 0)
            { return normalizedValuesList; }
            else
            { return null; }
        }

        /// <summary>
        /// Gets the permissible values for this non normalized question.
        /// </summary>
        /// <param name="dsFormInfo"></param>
        /// <param name="questionRow"></param>
        /// <returns>A DataView object containing all the pValueRows fro this question.</returns>
        public static DataView GetPermissibleValuesForThisNonNormalizedQuestion(MappingDataSet dsFormInfo, DataRow questionRow, MappingDataSet.ModulesListRow moduleRow)
        {
            DataView nonNormalizedValuesList = new DataView(dsFormInfo.NonNormalizedValuesList);
            nonNormalizedValuesList.RowFilter = "[Module Public ID]='" + questionRow["Module Public ID"] + "' AND "
                                                + "[Module Version]='" + questionRow["Module Version"] + "' AND "
                                                + "[CDE Public ID]='" + questionRow["CDE Public ID"] + "' AND "
                                                + "[CDE Version]='" + questionRow["CDE Version"] + "' AND"
                                                + "[caDSR Form Public ID]='" + moduleRow.caDSR_Form_Public_ID + "'";
            if (nonNormalizedValuesList.Count > 0)
            { return nonNormalizedValuesList; }
            else
            { return null; }
        }
        #endregion
    }
}
